package com.example.archive.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.archive.entity.PhysicalArchiveRetrievalRequest;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface PhysicalArchiveRetrievalRequestMapper extends BaseMapper<PhysicalArchiveRetrievalRequest> {
    
    @Select({
        "<script>",
        "SELECT r.*, ",
        "       u1.username as userName, ",
        "       u2.username as approverName, ",
        "       a.title as archiveTitle, ",
        "       a.archive_type as archiveType, ",
        "       a.physical_location as physicalLocation ",
        "FROM physical_archive_retrieval_requests r ",
        "LEFT JOIN users u1 ON r.user_id = u1.user_id ",
        "LEFT JOIN users u2 ON r.approver_id = u2.user_id ",
        "LEFT JOIN archives a ON r.archive_id = a.archive_id ",
        "WHERE 1=1 ",
        "<if test='userId != null'>",
        "   AND r.user_id = #{userId} ",
        "</if>",
        "<if test='status != null and status != \"\"'>",
        "   AND r.approval_status = #{status} ",
        "</if>",
        "<if test='search != null and search != \"\"'>",
        "   AND (a.title LIKE CONCAT('%', #{search}, '%') ",
        "        OR r.reason LIKE CONCAT('%', #{search}, '%') ",
        "        OR u1.username LIKE CONCAT('%', #{search}, '%')) ",
        "</if>",
        "ORDER BY r.request_time DESC",
        "</script>"
    })
    IPage<PhysicalArchiveRetrievalRequest> selectRequestsWithDetails(Page<PhysicalArchiveRetrievalRequest> page, 
                                                                    @Param("userId") Integer userId,
                                                                    @Param("status") String status,
                                                                    @Param("search") String search);
} 